import pymysql

db = pymysql.Connect(
    host='apiccr.megowx.com',
    port=3306,
    user='gupiao',
    passwd='Aitrip123',
    db='gupiao',
    charset='utf8'
)
cursor = db.cursor()


def setA5(data):
    global cursor
    global db

    # 上年奶牛+80， 连续一年+8， 亏则-60
    try:
        a5 = 0

        k = 36
        m = 80
        h = 60
        for i in range(0, 5):
            # 经营活动现金流净额
            # xj1_1 = float(data[1])
            xj1_2 = float(data[k])
            # 投资活动现金流净额
            # xj2_1 = float(data[3])
            xj2_2 = float(data[k + 6])
            # 筹资活动现金流净额
            # xj3_1 = float(data[5])
            xj3_2 = float(data[k + 12])

            # 奶牛型 + - -
            if xj1_2 > 0 and xj2_2 < 0 and xj3_2 < 0:
                a5 += m

            # 蛮牛型 + - +
            if xj1_2 > 0 and xj2_2 < 0 and xj3_2 > 0:
                a5 += h

            if xj1_2 < 0:
                a5 -= 60

            k += 1
            m = 8
            h = 6

        sql = "UPDATE `fa_stock_dfc` SET A5=" + str(a5) + " WHERE id=" + str(data[0])
        cursor.execute(sql)
        db.commit()

    except Exception as e:
        print(e)
        print(sql)
        return False

    return True


def setC0(data):
    global cursor
    global db

    close = 0
    try:
        sql = "SELECT close FROM `fa_stock_k` where code = '" + data[1] + "' order by id desc limit 1"
        result = cursor.execute(sql)

        if result:
            data1 = cursor.fetchone()
            close = float(data1[0])

    except Exception as e:
        print(e)

    c0 = 0
    # 自由现金年平均/总市值
    try:
        k = 36
        year = 0
        # 总股本
        zgb = data[53].replace(" 万股", "")
        # 总市值
        zsz = float(zgb) * 10000 * close
        zyxj = 0
        t = 1
        for i in range(0, 5):
            # data[k] = data[k].replace("--", "0")
            # data[k + 6] = data[k + 6].replace("--", "0")
            # 自由现金 万
            if float(data[k]) == 0:
                continue

            if float(data[k]) < 0:
                return False

            if year > 0 and float(data[k]) > float(data[k-1]):
                return False

            zyxj += (float(data[k]) + float(data[k + 6])) * t

            year += 1
            k += 1
            t = t * 0.9

        if year > 0:
            c0 = zyxj / year

        # 这个比例为整数
        c0 = c0 * 10000 * 100000000 / zsz

        sql = "UPDATE `fa_stock_dfc` SET A5=" + str(c0) + " WHERE id=" + str(data[0])
        cursor.execute(sql)
        db.commit()

    except Exception as e:
        print(e)
        print(sql)
        return False

    return True


def querydata(code):
    global cursor
    global db

    # code = 'sh.601258'
    try:
        sql = "SELECT * FROM `fa_stock_dfc` where code = '" + code + "'"
        result = cursor.execute(sql)

        if result:
            data = cursor.fetchone()
            setC0(data)

    except Exception as e:
        print(e)

    return True


code_list = []
try:
    sql = "SELECT code FROM `fa_stock_mk` where date = '2019-04-30' "
    result = cursor.execute(sql)

    if result:
        code_list = cursor.fetchall()

except Exception as e:
    print(e)

num = 0
isFirst = 0

for i in range(0, len(code_list)):
    one = code_list[i]
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1:
        result = querydata(one[0])
        if result == 0:
            continue
        print('----Finish---' + str(num))
        num += 1
        if one[0] == "sz.300773":
            break
        # if num > 0:
        #     break

cursor.close()
db.close()

print('----OK---')

# UPDATE fa_stock_dfc SET A5=8 WHERE id=1
#  SELECT A5 FROM `fa_stock_dfc` WHERE id=1
#  SELECT code, A5 FROM `fa_stock_dfc` WHERE A5 > 0 order by A5 desc
#  UPDATE fa_stock_dfc SET A5=0
#  SELECT code, gb1 FROM `fa_stock_dfc`
